package com.dtmobile.spark.biz.kpi

import org.apache.spark.sql.{SaveMode, SparkSession}

/**
  * Created by shenkaili on 17-3-31.
  */
class KpibusinessHourAnaly(ANALY_DATE: String, ANALY_HOUR: String, SDB: String, DDB: String, warhouseDir: String,versiononoff:Int) {
  val cal_date = ANALY_DATE.substring(0, 4) + "-" + ANALY_DATE.substring(4).substring(0,2) + "-" + ANALY_DATE.substring(6) + " " + String.valueOf(ANALY_HOUR) + ":00:00"
  var onoff=versiononoff
  if(versiononoff!=0 && versiononoff!=1){
    onoff=0
  }
  val kpibusinesssum=
    s"""
       |sum(browsedownloadvisits),
       |sum(videoservicevisits),
       |sum(instantmessagevisits),
       |sum(appvisits),
       |sum(browsedownloadbusiness),
       |sum(videoservicebusiness),
       |sum(instantmessagebusiness),
       |sum(appbusiness),
       |sum(dnsQuerySucc),
       |sum(dnsQueryAtt),
       |sum(tcpSetupSucc),
       |sum(tcpSetupReq),
       |sum(BearerULTCPRetransmit),
       |sum(BearerULTCPTransmit),
       |sum(BearerDLTCPRetransmit),
       |sum(BearerDLTCPTransmit),
       |sum(BearerULTCPMissequence),
       |sum(BearerDLTCPMissequence),
       |sum(pageresp),
       |sum(pagereq),
       |sum(pageresptimeall),
       |sum(pageshowsucc),
       |sum(pageshowtimeall),
       |sum(httpdownflow),
       |sum(httpdowntime),
       |sum(mediasucc),
       |sum(mediareq),
       |sum(mediadownflow),
       |sum(mediadowntime),
       |sum(ServiceIMSucc),
       |sum(ServiceIMReq),
       |sum(readvisits),
       |sum(wbvisits),
       |sum(navigationvisits),
       |sum(musicvisits),
       |sum(gamevisits),
       |sum(payvisits),
       |sum(Animevisits),
       |sum(mailvisits),
       |sum(p2pvisits),
       |sum(voipvisits),
       |sum(MultimediaMsgvisits),
       |sum(financialvisits),
       |sum(securityvisits),
       |sum(shoppingvisits),
       |sum(travelvisits),
       |sum(cloudstoragevisits),
       |sum(othervisits),
       |sum(readbusiness),
       |sum(wbbusiness),
       |sum(navigationbusiness),
       |sum(musicbusiness),
       |sum(gamebusiness),
       |sum(paybusiness),
       |sum(Animebusiness),
       |sum(mailbusiness),
       |sum(p2pbusiness),
       |sum(voipbusiness),
       |sum(MultimediaMsgbusiness),
       |sum(financialbusiness),
       |sum(securitybusiness),
       |sum(shoppingbusiness),
       |sum(travelbusiness),
       |sum(cloudstoragebusiness),
       |sum(otherbusiness),
       |sum(mediaRespTimeall),
       |sum(mediaResp),
       |sum(ServiceIMTrans),
       |sum(ServiceIMFlow),
       |sum(ServiceIMTime),
       |sum(readsucc),
       |sum(readreq),
       |sum(readresptimeall),
       |sum(readresp),
       |sum(readdownflow),
       |sum(readdowntime),
       |sum(wbsucc),
       |sum(wbreq),
       |sum(wbresptimeall),
       |sum(wbresp),
       |sum(wbdownflow),
       |sum(wbdowntime),
       |sum(navigationsucc),
       |sum(navigationreq),
       |sum(navigationresptimeall),
       |sum(navigationresp),
       |sum(navigationdownflow),
       |sum(navigationdowntime),
       |sum(musicsucc),
       |sum(musicreq),
       |sum(musicresptimeall),
       |sum(musicresp),
       |sum(musicdownflow),
       |sum(musicdowntime),
       |sum(appsucc),
       |sum(appreq),
       |sum(appresptimeall),
       |sum(appresp),
       |sum(appdownflow),
       |sum(appdowntime),
       |sum(gamesucc),
       |sum(gamereq),
       |sum(gameresptimeall),
       |sum(gameresp),
       |sum(gamedownflow),
       |sum(gamedowntime),
       |sum(paysucc),
       |sum(payreq),
       |sum(payresptimeall),
       |sum(payresp),
       |sum(paydownflow),
       |sum(paydowntime),
       |sum(Animesucc),
       |sum(Animereq),
       |sum(Animeresptimeall),
       |sum(Animeresp),
       |sum(Animedownflow),
       |sum(Animedowntime),
       |sum(mailsucc),
       |sum(mailreq),
       |sum(mailresptimeall),
       |sum(mailresp),
       |sum(maildownflow),
       |sum(maildowntime),
       |sum(P2Psucc),
       |sum(P2Preq),
       |sum(P2Presptimeall),
       |sum(P2Presp),
       |sum(P2Pdownflow),
       |sum(P2Pdowntime),
       |sum(VoIPsucc),
       |sum(VoIPreq),
       |sum(VoIPresptimeall),
       |sum(VoIPresp),
       |sum(VoIPdownflow),
       |sum(VoIPdowntime),
       |sum(MultimediaMsgsucc),
       |sum(MultimediaMsgreq),
       |sum(MultimediaMsgresptimeall),
       |sum(MultimediaMsgresp),
       |sum(MultimediaMsgdownflow),
       |sum(MultimediaMsgdowntime),
       |sum(financialsucc),
       |sum(financialreq),
       |sum(financialresptimeall),
       |sum(financialresp),
       |sum(financialdownflow),
       |sum(financialdowntime),
       |sum(securitysucc),
       |sum(securityreq),
       |sum(securityresptimeall),
       |sum(securityresp),
       |sum(securitydownflow),
       |sum(securitydowntime),
       |sum(shoppingsucc),
       |sum(shoppingreq),
       |sum(shoppingresptimeall),
       |sum(shoppingresp),
       |sum(shoppingdownflow),
       |sum(shoppingdowntime),
       |sum(travelsucc),
       |sum(travelreq),
       |sum(travelresptimeall),
       |sum(travelresp),
       |sum(traveldownflow),
       |sum(traveldowntime),
       |sum(cloudstoragesucc),
       |sum(cloudstoragereq),
       |sum(cloudstorageresptimeall),
       |sum(cloudstorageresp),
       |sum(cloudstoragedownflow),
       |sum(cloudstoragedowntime),
       |sum(internetsucc),
       |sum(internetreq),
       |sum(internetresptimeall),
       |sum(internetresp),
       |sum(internetdownflow),
       |sum(internetdowntime),
       |sum(migusucc),
       |sum(migureq),
       |sum(miguresptimeall),
       |sum(miguresp),
       |sum(migudownflow),
       |sum(migudowntime),
       |sum(othersucc),
       |sum(otherreq),
       |sum(otherresptimeall),
       |sum(otherresp),
       |sum(otherdownflow),
       |sum(otherdowntime),
       |sum(instantmessageTime),
       |sum(readTime),
       |sum(wbTime),
       |sum(navigationTime),
       |sum(videoserviceTime),
       |sum(musicTime),
       |sum(appTime),
       |sum(gameTime),
       |sum(payTime),
       |sum(AnimeTime),
       |sum(mailTime),
       |sum(p2pTime),
       |sum(voipTime),
       |sum(MultimediaMsgTime),
       |sum(browsedownloadTime),
       |sum(financialTime),
       |sum(securityTime),
       |sum(shoppingTime),
       |sum(travelTime),
       |sum(cloudstorageTime),
       |sum(internetTime),
       |sum(miguTime),
       |sum(otherTime),
       |sum(browsesurfresp),
       |sum(browsesurfreq),
       |sum(browsesurfresptimeall),
       |sum(browsesurfshowsucc),
       |sum(browsesurfshowtimeall),
       |sum(browsesurfdownflow),
       |sum(browsesurfdowntime),
       |sum(browsesurfFlow),
       |sum(browsesurfTime),
       |sum(browsetencentresp),
       |sum(browsetencentreq),
       |sum(browsetencentresptimeall),
       |sum(browsetencentshowsucc),
       |sum(browsetencentshowtimeall),
       |sum(browsetencentdownflow),
       |sum(browsetencentdowntime),
       |sum(browsetencentFlow),
       |sum(browsetencentTime),
       |sum(browseqqNewsresp),
       |sum(browseqqNewsreq),
       |sum(browseqqNewsresptimeall),
       |sum(browseqqNewsshowsucc),
       |sum(browseqqNewsshowtimeall),
       |sum(browseqqNewsdownflow),
       |sum(browseqqNewsdowntime),
       |sum(browseqqNewsFlow),
       |sum(browseqqNewsTime),
       |sum(browsetoutiaoresp),
       |sum(browsetoutiaoreq),
       |sum(browsetoutiaoresptimeall),
       |sum(browsetoutiaoshowsucc),
       |sum(browsetoutiaoshowtimeall),
       |sum(browsetoutiaodownflow),
       |sum(browsetoutiaodowntime),
       |sum(browsetoutiaoFlow),
       |sum(browsetoutiaoTime),
       |sum(browseChromeresp),
       |sum(browseChromereq),
       |sum(browseChromeresptimeall),
       |sum(browseChromeshowsucc),
       |sum(browseChromeshowtimeall),
       |sum(browseChromedownflow),
       |sum(browseChromedowntime),
       |sum(browseChromeFlow),
       |sum(browseChromeTime),
       |sum(otherhttpresp),
       |sum(otherhttpreq),
       |sum(otherhttpresptimeall),
       |sum(otherhttpshowsucc),
       |sum(otherhttpshowtimeall),
       |sum(otherhttpdownflow),
       |sum(otherhttpdowntime),
       |sum(otherhttpFlow),
       |sum(otherhttpTime),
       |sum(videomobileTVsucc),
       |sum(videomobileTVreq),
       |sum(videomobileTVresptimeall),
       |sum(videomobileTVresp),
       |sum(videomobileTVdownflow),
       |sum(videomobileTVdowntime),
       |sum(videomobileTVFlow),
       |sum(videomobileTVTime),
       |sum(videomiguvideosucc),
       |sum(videomiguvideoreq),
       |sum(videomiguvideoresptimeall),
       |sum(videomiguvideoresp),
       |sum(videomiguvideodownflow),
       |sum(videomiguvideodowntime),
       |sum(videomiguvideoFlow),
       |sum(videomiguvideoTime),
       |sum(videoiqiyisucc),
       |sum(videoiqiyireq),
       |sum(videoiqiyiresptimeall),
       |sum(videoiqiyiresp),
       |sum(videoiqiyidownflow),
       |sum(videoiqiyidowntime),
       |sum(videoiqiyiFlow),
       |sum(videoiqiyiTime),
       |sum(imFechatsucc),
       |sum(imFechatreq),
       |sum(imFechatresptimeall),
       |sum(imFechatresp),
       |sum(imFechatdownflow),
       |sum(imFechatdowntime),
       |sum(imFechatFlow),
       |sum(imFechatTime),
       |sum(imFetionsucc),
       |sum(imFetionreq),
       |sum(imFetionresptimeall),
       |sum(imFetionresp),
       |sum(imFetiondownflow),
       |sum(imFetiondowntime),
       |sum(imFetionFlow),
       |sum(imFetionTime),
       |sum(imQQsucc),
       |sum(imQQreq),
       |sum(imQQresptimeall),
       |sum(imQQresp),
       |sum(imQQdownflow),
       |sum(imQQdowntime),
       |sum(imQQFlow),
       |sum(imQQTime),
       |sum(imwechatsucc),
       |sum(imwechatreq),
       |sum(imwechatresptimeall),
       |sum(imwechatresp),
       |sum(imwechatdownflow),
       |sum(imwechatdowntime),
       |sum(imwechatFlow),
       |sum(imwechatTime),
       |sum(readcmreadsucc),
       |sum(readcmreadreq),
       |sum(readcmreadresptimeall),
       |sum(readcmreadresp),
       |sum(readcmreaddownflow),
       |sum(readcmreaddowntime),
       |sum(readcmreadFlow),
       |sum(readcmreadTime),
       |sum(readsurfsucc),
       |sum(readsurfreq),
       |sum(readsurfresptimeall),
       |sum(readsurfresp),
       |sum(readsurfdownflow),
       |sum(readsurfdowntime),
       |sum(readsurtFlow),
       |sum(readsurfTime),
       |sum(readQQReadsucc),
       |sum(readQQReadreq),
       |sum(readQQReadresptimeall),
       |sum(readQQReadresp),
       |sum(readQQReaddownflow),
       |sum(readQQReaddowntime),
       |sum(readQQReadFlow),
       |sum(readQQReadTime),
       |sum(weibo139wbsucc),
       |sum(weibo139wbreq),
       |sum(weibo139wbresptimeall),
       |sum(weibo139wbresp),
       |sum(weibo139wbdownflow),
       |sum(weibo139wbdowntime),
       |sum(weibo139wbFlow),
       |sum(weibo139wbTime),
       |sum(weibosinaWeibosucc),
       |sum(weibosinaWeiboreq),
       |sum(weibosinaWeiboresptimeall),
       |sum(weibosinaWeiboresp),
       |sum(weibosinaWeibodownflow),
       |sum(weibosinaWeibodowntime),
       |sum(weibosinaWeiboFlow),
       |sum(weibosinaWeiboTime),
       |sum(navigationheMapsucc),
       |sum(navigationheMapreq),
       |sum(navigationheMapresptimeall),
       |sum(navigationheMapresp),
       |sum(navigationheMapdownflow),
       |sum(navigationheMapdowntime),
       |sum(navigationheMapFlow),
       |sum(navigationheMapTime),
       |sum(navigationaMapsucc),
       |sum(navigationaMapreq),
       |sum(navigationaMapresptimeall),
       |sum(navigationaMapresp),
       |sum(navigationaMapdownflow),
       |sum(navigationaMapdowntime),
       |sum(navigationaMapFlow),
       |sum(navigationaMapTime),
       |sum(musicmigumusicsucc),
       |sum(musicmigumusicreq),
       |sum(musicmigumusicresptimeall),
       |sum(musicmigumusicresp),
       |sum(musicmigumusicdownflow),
       |sum(musicmigumusicdowntime),
       |sum(musicmigumusicFlow),
       |sum(musicmigumusicTime),
       |sum(musicqqmusicsucc),
       |sum(musicqqmusicreq),
       |sum(musicqqmusicresptimeall),
       |sum(musicqqmusicresp),
       |sum(musicqqmusicdownflow),
       |sum(musicqqmusicdowntime),
       |sum(musicqqmusicFlow),
       |sum(musicqqmusicTime),
       |sum(AppStoreMobileMarketsucc),
       |sum(AppStoreMobileMarketreq),
       |sum(AppStoreMobileMarketresptimeall),
       |sum(AppStoreMobileMarketresp),
       |sum(AppStoreMobileMarketdownflow),
       |sum(AppStoreMobileMarketdowntime),
       |sum(AppStoreMobileMarketFlow),
       |sum(AppStoreMobileMarketTime),
       |sum(AppStoreAppStoresucc),
       |sum(AppStoreAppStorereq),
       |sum(AppStoreAppStoreresptimeall),
       |sum(AppStoreAppStoreresp),
       |sum(AppStoreAppStoredownflow),
       |sum(AppStoreAppStoredowntime),
       |sum(AppStoreAppStoreFlow),
       |sum(AppStoreAppStoreTime),
       |sum(GamemiguGamesucc),
       |sum(GamemiguGamereq),
       |sum(GamemiguGameresptimeall),
       |sum(GamemiguGameresp),
       |sum(GamemiguGamedownflow),
       |sum(GamemiguGamedowntime),
       |sum(GamemiguGameFlow),
       |sum(GamemiguGameTime),
       |sum(Game4399Gamesucc),
       |sum(Game4399Gamereq),
       |sum(Game4399Gameresptimeall),
       |sum(Game4399Gameresp),
       |sum(Game4399Gamedownflow),
       |sum(Game4399Gamedowntime),
       |sum(Game4399GameFlow),
       |sum(Game4399GameTime),
       |sum(PayNFCSIMsucc),
       |sum(PayNFCSIMreq),
       |sum(PayNFCSIMresptimeall),
       |sum(PayNFCSIMresp),
       |sum(PayNFCSIMdownflow),
       |sum(PayNFCSIMdowntime),
       |sum(PayNFCSIMFlow),
       |sum(PayNFCSIMTime),
       |sum(PaymobilePaysucc),
       |sum(PaymobilePayreq),
       |sum(PaymobilePayresptimeall),
       |sum(PaymobilePayresp),
       |sum(PaymobilePaydownflow),
       |sum(PaymobilePaydowntime),
       |sum(PaymobilePayFlow),
       |sum(PaymobilePayTime),
       |sum(PayNFCsucc),
       |sum(PayNFCreq),
       |sum(PayNFCresptimeall),
       |sum(PayNFCresp),
       |sum(PayNFCdownflow),
       |sum(PayNFCdowntime),
       |sum(PayNFCFlow),
       |sum(PayNFCTime),
       |sum(Animemigudmsucc),
       |sum(Animemigudmreq),
       |sum(Animemigudmresptimeall),
       |sum(Animemigudmresp),
       |sum(Animemigudmdownflow),
       |sum(Animemigudmdowntime),
       |sum(AnimemigudmFlow),
       |sum(AnimemigudmTime),
       |sum(AnimeqqComicsucc),
       |sum(AnimeqqComicreq),
       |sum(AnimeqqComicresptimeall),
       |sum(AnimeqqComicresp),
       |sum(AnimeqqComicdownflow),
       |sum(AnimeqqComicdowntime),
       |sum(AnimeqqComicFlow),
       |sum(AnimeqqComicTime),
       |sum(mail139mailsucc),
       |sum(mail139mailreq),
       |sum(mail139mailresptimeall),
       |sum(mail139mailresp),
       |sum(mail139maildownflow),
       |sum(mail139maildowntime),
       |sum(mail139mailFlow),
       |sum(mail139mailTime),
       |sum(MailneteaseMailsucc),
       |sum(MailneteaseMailreq),
       |sum(MailneteaseMailresptimeall),
       |sum(MailneteaseMailresp),
       |sum(MailneteaseMaildownflow),
       |sum(MailneteaseMaildowntime),
       |sum(MailneteaseMailFlow),
       |sum(MailneteaseMailTime),
       |sum(financialsjsjsucc),
       |sum(financialsjsjreq),
       |sum(financialsjsjresptimeall),
       |sum(financialsjsjresp),
       |sum(financialsjsjdownflow),
       |sum(financialsjsjdowntime),
       |sum(financialsjsjFlow),
       |sum(financialsjsjTime),
       |sum(financialsjzqsucc),
       |sum(financialsjzqreq),
       |sum(financialsjzqresptimeall),
       |sum(financialsjzqresp),
       |sum(financialsjzqdownflow),
       |sum(financialsjzqdowntime),
       |sum(financialsjzqFlow),
       |sum(financialsjzqTime),
       |sum(financialzixuangusucc),
       |sum(financialzixuangureq),
       |sum(financialzixuanguresptimeall),
       |sum(financialzixuanguresp),
       |sum(financialzixuangudownflow),
       |sum(financialzixuangudowntime),
       |sum(financialzixuanguFlow),
       |sum(financialzixuanguTime),
       |sum(Othergdqqtsucc),
       |sum(Othergdqqtreq),
       |sum(Othergdqqtresptimeall),
       |sum(Othergdqqtresp),
       |sum(Othergdqqtdownflow),
       |sum(Othergdqqtdowntime),
       |sum(OthergdqqtFlow),
       |sum(OthergdqqtTime),
       |sum(Otherydzssucc),
       |sum(Otherydzsreq),
       |sum(Otherydzsresptimeall),
       |sum(Otherydzsresp),
       |sum(Otherydzsdownflow),
       |sum(Otherydzsdowntime),
       |sum(OtherydzsFlow),
       |sum(OtherydzsTime),
       |sum(Othernxbstsucc),
       |sum(Othernxbstreq),
       |sum(Othernxbstresptimeall),
       |sum(Othernxbstresp),
       |sum(Othernxbstdownflow),
       |sum(Othernxbstdowntime),
       |sum(OthernxbstFlow),
       |sum(OthernxbstTime),
       |sum(Othernxtsucc),
       |sum(Othernxtreq),
       |sum(Othernxtresptimeall),
       |sum(Othernxtresp),
       |sum(Othernxtdownflow),
       |sum(Othernxtdowntime),
       |sum(OthernxtFlow),
       |sum(OthernxtTime),
       |sum(Otheryjtsucc),
       |sum(Otheryjtreq),
       |sum(Otheryjtresptimeall),
       |sum(Otheryjtresp),
       |sum(Otheryjtdownflow),
       |sum(Otheryjtdowntime),
       |sum(OtheryjtFlow),
       |sum(OtheryjtTime),
       |sum(internetvisits),
       |sum(miguvisits),
       |sum(internetbusiness),
       |sum(migubusiness),
       |sum(ServiceIMresptimeall)
     """.stripMargin
  val busniessDns=
    s"""
       |0 as browsedownloadvisits,
       |0 as videoservicevisits,
       |0 as instantmessagevisits,
       |0 as appvisits,
       |0 as browsedownloadbusiness,
       |0 as videoservicebusiness,
       |0 as instantmessagebusiness,
       |0 as appbusiness,
       |sum(case when apptypecode=101 and dnsrecode =0 then 1 else 0 end)dnsQuerySucc,
       |sum(case when apptypecode=101 then 1 else 0 end)dnsQueryAtt,
       |0 as tcpSetupSucc,
       |0 as tcpSetupReq,
       |0 as BearerULTCPRetransmit,
       |0 as BearerULTCPTransmit,
       |0 as BearerDLTCPRetransmit,
       |0 as BearerDLTCPTransmit,
       |0 as BearerULTCPMissequence,
       |0 as BearerDLTCPMissequence,
       |0 as pageresp,
       |0 as pagereq,
       |0 as pageresptimeall,
       |0 as pageshowsucc,
       |0 as pageshowtimeall,
       |0 as httpdownflow,
       |0 as httpdowntime,
       |0 as mediasucc,
       |0 as mediareq,
       |0 as mediadownflow,
       |0 as mediadowntime,
       |0 as ServiceIMSucc,
       |0 as ServiceIMReq,
       |0 as readvisits,
       |0 as wbvisits,
       |0 as navigationvisits,
       |0 as musicvisits,
       |0 as gamevisits,
       |0 as payvisits,
       |0 as Animevisits,
       |0 as mailvisits,
       |0 as p2pvisits,
       |0 as voipvisits,
       |0 as MultimediaMsgvisits,
       |0 as financialvisits,
       |0 as securityvisits,
       |0 as shoppingvisits,
       |0 as travelvisits,
       |0 as cloudstoragevisits,
       |0 as othervisits,
       |0 as readbusiness,
       |0 as wbbusiness,
       |0 as navigationbusiness,
       |0 as musicbusiness,
       |0 as gamebusiness,
       |0 as paybusiness,
       |0 as Animebusiness,
       |0 as mailbusiness,
       |0 as p2pbusiness,
       |0 as voipbusiness,
       |0 as MultimediaMsgbusiness,
       |0 as financialbusiness,
       |0 as securitybusiness,
       |0 as shoppingbusiness,
       |0 as travelbusiness,
       |0 as cloudstoragebusiness,
       |0 as otherbusiness,
       |0 as mediaRespTimeall,
       |0 as mediaResp,
       |0 as ServiceIMTrans,
       |0 as ServiceIMFlow,
       |0 as ServiceIMTime,
       |0 as readsucc,
       |0 as readreq,
       |0 as readresptimeall,
       |0 as readresp,
       |0 as readdownflow,
       |0 as readdowntime,
       |0 as wbsucc,
       |0 as wbreq,
       |0 as wbresptimeall,
       |0 as wbresp,
       |0 as wbdownflow,
       |0 as wbdowntime,
       |0 as navigationsucc,
       |0 as navigationreq,
       |0 as navigationresptimeall,
       |0 as navigationresp,
       |0 as navigationdownflow,
       |0 as navigationdowntime,
       |0 as musicsucc,
       |0 as musicreq,
       |0 as musicresptimeall,
       |0 as musicresp,
       |0 as musicdownflow,
       |0 as musicdowntime,
       |0 as appsucc,
       |0 as appreq,
       |0 as appresptimeall,
       |0 as appresp,
       |0 as appdownflow,
       |0 as appdowntime,
       |0 as gamesucc,
       |0 as gamereq,
       |0 as gameresptimeall,
       |0 as gameresp,
       |0 as gamedownflow,
       |0 as gamedowntime,
       |0 as paysucc,
       |0 as payreq,
       |0 as payresptimeall,
       |0 as payresp,
       |0 as paydownflow,
       |0 as paydowntime,
       |0 as Animesucc,
       |0 as Animereq,
       |0 as Animeresptimeall,
       |0 as Animeresp,
       |0 as Animedownflow,
       |0 as Animedowntime,
       |0 as mailsucc,
       |0 as mailreq,
       |0 as mailresptimeall,
       |0 as mailresp,
       |0 as maildownflow,
       |0 as maildowntime,
       |0 as P2Psucc,
       |0 as P2Preq,
       |0 as P2Presptimeall,
       |0 as P2Presp,
       |0 as P2Pdownflow,
       |0 as P2Pdowntime,
       |0 as VoIPsucc,
       |0 as VoIPreq,
       |0 as VoIPresptimeall,
       |0 as VoIPresp,
       |0 as VoIPdownflow,
       |0 as VoIPdowntime,
       |0 as MultimediaMsgsucc,
       |0 as MultimediaMsgreq,
       |0 as MultimediaMsgresptimeall,
       |0 as MultimediaMsgresp,
       |0 as MultimediaMsgdownflow,
       |0 as MultimediaMsgdowntime,
       |0 as financialsucc,
       |0 as financialreq,
       |0 as financialresptimeall,
       |0 as financialresp,
       |0 as financialdownflow,
       |0 as financialdowntime,
       |0 as securitysucc,
       |0 as securityreq,
       |0 as securityresptimeall,
       |0 as securityresp,
       |0 as securitydownflow,
       |0 as securitydowntime,
       |0 as shoppingsucc,
       |0 as shoppingreq,
       |0 as shoppingresptimeall,
       |0 as shoppingresp,
       |0 as shoppingdownflow,
       |0 as shoppingdowntime,
       |0 as travelsucc,
       |0 as travelreq,
       |0 as travelresptimeall,
       |0 as travelresp,
       |0 as traveldownflow,
       |0 as traveldowntime,
       |0 as cloudstoragesucc,
       |0 as cloudstoragereq,
       |0 as cloudstorageresptimeall,
       |0 as cloudstorageresp,
       |0 as cloudstoragedownflow,
       |0 as cloudstoragedowntime,
       |0 as internetsucc,
       |0 as internetreq,
       |0 as internetresptimeall,
       |0 as internetresp,
       |0 as internetdownflow,
       |0 as internetdowntime,
       |0 as migusucc,
       |0 as migureq,
       |0 as miguresptimeall,
       |0 as miguresp,
       |0 as migudownflow,
       |0 as migudowntime,
       |0 as othersucc,
       |0 as otherreq,
       |0 as otherresptimeall,
       |0 as otherresp,
       |0 as otherdownflow,
       |0 as otherdowntime,
       |0 as instantmessageTime,
       |0 as readTime,
       |0 as wbTime,
       |0 as navigationTime,
       |0 as videoserviceTime,
       |0 as musicTime,
       |0 as appTime,
       |0 as gameTime,
       |0 as payTime,
       |0 as AnimeTime,
       |0 as mailTime,
       |0 as p2pTime,
       |0 as voipTime,
       |0 as MultimediaMsgTime,
       |0 as browsedownloadTime,
       |0 as financialTime,
       |0 as securityTime,
       |0 as shoppingTime,
       |0 as travelTime,
       |0 as cloudstorageTime,
       |0 as internetTime,
       |0 as miguTime,
       |0 as otherTime,
       |0 as browsesurfresp,
       |0 as browsesurfreq,
       |0 as browsesurfresptimeall,
       |0 as browsesurfshowsucc,
       |0 as browsesurfshowtimeall,
       |0 as browsesurfdownflow,
       |0 as browsesurfdowntime,
       |0 as browsesurfFlow,
       |0 as browsesurfTime,
       |0 as browsetencentresp,
       |0 as browsetencentreq,
       |0 as browsetencentresptimeall,
       |0 as browsetencentshowsucc,
       |0 as browsetencentshowtimeall,
       |0 as browsetencentdownflow,
       |0 as browsetencentdowntime,
       |0 as browsetencentFlow,
       |0 as browsetencentTime,
       |0 as browseqqNewsresp,
       |0 as browseqqNewsreq,
       |0 as browseqqNewsresptimeall,
       |0 as browseqqNewsshowsucc,
       |0 as browseqqNewsshowtimeall,
       |0 as browseqqNewsdownflow,
       |0 as browseqqNewsdowntime,
       |0 as browseqqNewsFlow,
       |0 as browseqqNewsTime,
       |0 as browsetoutiaoresp,
       |0 as browsetoutiaoreq,
       |0 as browsetoutiaoresptimeall,
       |0 as browsetoutiaoshowsucc,
       |0 as browsetoutiaoshowtimeall,
       |0 as browsetoutiaodownflow,
       |0 as browsetoutiaodowntime,
       |0 as browsetoutiaoFlow,
       |0 as browsetoutiaoTime,
       |0 as browseChromeresp,
       |0 as browseChromereq,
       |0 as browseChromeresptimeall,
       |0 as browseChromeshowsucc,
       |0 as browseChromeshowtimeall,
       |0 as browseChromedownflow,
       |0 as browseChromedowntime,
       |0 as browseChromeFlow,
       |0 as browseChromeTime,
       |0 as otherhttpresp,
       |0 as otherhttpreq,
       |0 as otherhttpresptimeall,
       |0 as otherhttpshowsucc,
       |0 as otherhttpshowtimeall,
       |0 as otherhttpdownflow,
       |0 as otherhttpdowntime,
       |0 as otherhttpFlow,
       |0 as otherhttpTime,
       |0 as videomobileTVsucc,
       |0 as videomobileTVreq,
       |0 as videomobileTVresptimeall,
       |0 as videomobileTVresp,
       |0 as videomobileTVdownflow,
       |0 as videomobileTVdowntime,
       |0 as videomobileTVFlow,
       |0 as videomobileTVTime,
       |0 as videomiguvideosucc,
       |0 as videomiguvideoreq,
       |0 as videomiguvideoresptimeall,
       |0 as videomiguvideoresp,
       |0 as videomiguvideodownflow,
       |0 as videomiguvideodowntime,
       |0 as videomiguvideoFlow,
       |0 as videomiguvideoTime,
       |0 as videoiqiyisucc,
       |0 as videoiqiyireq,
       |0 as videoiqiyiresptimeall,
       |0 as videoiqiyiresp,
       |0 as videoiqiyidownflow,
       |0 as videoiqiyidowntime,
       |0 as videoiqiyiFlow,
       |0 as videoiqiyiTime,
       |0 as imFechatsucc,
       |0 as imFechatreq,
       |0 as imFechatresptimeall,
       |0 as imFechatresp,
       |0 as imFechatdownflow,
       |0 as imFechatdowntime,
       |0 as imFechatFlow,
       |0 as imFechatTime,
       |0 as imFetionsucc,
       |0 as imFetionreq,
       |0 as imFetionresptimeall,
       |0 as imFetionresp,
       |0 as imFetiondownflow,
       |0 as imFetiondowntime,
       |0 as imFetionFlow,
       |0 as imFetionTime,
       |0 as imQQsucc,
       |0 as imQQreq,
       |0 as imQQresptimeall,
       |0 as imQQresp,
       |0 as imQQdownflow,
       |0 as imQQdowntime,
       |0 as imQQFlow,
       |0 as imQQTime,
       |0 as imwechatsucc,
       |0 as imwechatreq,
       |0 as imwechatresptimeall,
       |0 as imwechatresp,
       |0 as imwechatdownflow,
       |0 as imwechatdowntime,
       |0 as imwechatFlow,
       |0 as imwechatTime,
       |0 as readcmreadsucc,
       |0 as readcmreadreq,
       |0 as readcmreadresptimeall,
       |0 as readcmreadresp,
       |0 as readcmreaddownflow,
       |0 as readcmreaddowntime,
       |0 as readcmreadFlow,
       |0 as readcmreadTime,
       |0 as readsurfsucc,
       |0 as readsurfreq,
       |0 as readsurfresptimeall,
       |0 as readsurfresp,
       |0 as readsurfdownflow,
       |0 as readsurfdowntime,
       |0 as readsurtFlow,
       |0 as readsurfTime,
       |0 as readQQReadsucc,
       |0 as readQQReadreq,
       |0 as readQQReadresptimeall,
       |0 as readQQReadresp,
       |0 as readQQReaddownflow,
       |0 as readQQReaddowntime,
       |0 as readQQReadFlow,
       |0 as readQQReadTime,
       |0 as weibo139wbsucc,
       |0 as weibo139wbreq,
       |0 as weibo139wbresptimeall,
       |0 as weibo139wbresp,
       |0 as weibo139wbdownflow,
       |0 as weibo139wbdowntime,
       |0 as weibo139wbFlow,
       |0 as weibo139wbTime,
       |0 as weibosinaWeibosucc,
       |0 as weibosinaWeiboreq,
       |0 as weibosinaWeiboresptimeall,
       |0 as weibosinaWeiboresp,
       |0 as weibosinaWeibodownflow,
       |0 as weibosinaWeibodowntime,
       |0 as weibosinaWeiboFlow,
       |0 as weibosinaWeiboTime,
       |0 as navigationheMapsucc,
       |0 as navigationheMapreq,
       |0 as navigationheMapresptimeall,
       |0 as navigationheMapresp,
       |0 as navigationheMapdownflow,
       |0 as navigationheMapdowntime,
       |0 as navigationheMapFlow,
       |0 as navigationheMapTime,
       |0 as navigationaMapsucc,
       |0 as navigationaMapreq,
       |0 as navigationaMapresptimeall,
       |0 as navigationaMapresp,
       |0 as navigationaMapdownflow,
       |0 as navigationaMapdowntime,
       |0 as navigationaMapFlow,
       |0 as navigationaMapTime,
       |0 as musicmigumusicsucc,
       |0 as musicmigumusicreq,
       |0 as musicmigumusicresptimeall,
       |0 as musicmigumusicresp,
       |0 as musicmigumusicdownflow,
       |0 as musicmigumusicdowntime,
       |0 as musicmigumusicFlow,
       |0 as musicmigumusicTime,
       |0 as musicqqmusicsucc,
       |0 as musicqqmusicreq,
       |0 as musicqqmusicresptimeall,
       |0 as musicqqmusicresp,
       |0 as musicqqmusicdownflow,
       |0 as musicqqmusicdowntime,
       |0 as musicqqmusicFlow,
       |0 as musicqqmusicTime,
       |0 as AppStoreMobileMarketsucc,
       |0 as AppStoreMobileMarketreq,
       |0 as AppStoreMobileMarketresptimeall,
       |0 as AppStoreMobileMarketresp,
       |0 as AppStoreMobileMarketdownflow,
       |0 as AppStoreMobileMarketdowntime,
       |0 as AppStoreMobileMarketFlow,
       |0 as AppStoreMobileMarketTime,
       |0 as AppStoreAppStoresucc,
       |0 as AppStoreAppStorereq,
       |0 as AppStoreAppStoreresptimeall,
       |0 as AppStoreAppStoreresp,
       |0 as AppStoreAppStoredownflow,
       |0 as AppStoreAppStoredowntime,
       |0 as AppStoreAppStoreFlow,
       |0 as AppStoreAppStoreTime,
       |0 as GamemiguGamesucc,
       |0 as GamemiguGamereq,
       |0 as GamemiguGameresptimeall,
       |0 as GamemiguGameresp,
       |0 as GamemiguGamedownflow,
       |0 as GamemiguGamedowntime,
       |0 as GamemiguGameFlow,
       |0 as GamemiguGameTime,
       |0 as Game4399Gamesucc,
       |0 as Game4399Gamereq,
       |0 as Game4399Gameresptimeall,
       |0 as Game4399Gameresp,
       |0 as Game4399Gamedownflow,
       |0 as Game4399Gamedowntime,
       |0 as Game4399GameFlow,
       |0 as Game4399GameTime,
       |0 as PayNFCSIMsucc,
       |0 as PayNFCSIMreq,
       |0 as PayNFCSIMresptimeall,
       |0 as PayNFCSIMresp,
       |0 as PayNFCSIMdownflow,
       |0 as PayNFCSIMdowntime,
       |0 as PayNFCSIMFlow,
       |0 as PayNFCSIMTime,
       |0 as PaymobilePaysucc,
       |0 as PaymobilePayreq,
       |0 as PaymobilePayresptimeall,
       |0 as PaymobilePayresp,
       |0 as PaymobilePaydownflow,
       |0 as PaymobilePaydowntime,
       |0 as PaymobilePayFlow,
       |0 as PaymobilePayTime,
       |0 as PayNFCsucc,
       |0 as PayNFCreq,
       |0 as PayNFCresptimeall,
       |0 as PayNFCresp,
       |0 as PayNFCdownflow,
       |0 as PayNFCdowntime,
       |0 as PayNFCFlow,
       |0 as PayNFCTime,
       |0 as Animemigudmsucc,
       |0 as Animemigudmreq,
       |0 as Animemigudmresptimeall,
       |0 as Animemigudmresp,
       |0 as Animemigudmdownflow,
       |0 as Animemigudmdowntime,
       |0 as AnimemigudmFlow,
       |0 as AnimemigudmTime,
       |0 as AnimeqqComicsucc,
       |0 as AnimeqqComicreq,
       |0 as AnimeqqComicresptimeall,
       |0 as AnimeqqComicresp,
       |0 as AnimeqqComicdownflow,
       |0 as AnimeqqComicdowntime,
       |0 as AnimeqqComicFlow,
       |0 as AnimeqqComicTime,
       |0 as mail139mailsucc,
       |0 as mail139mailreq,
       |0 as mail139mailresptimeall,
       |0 as mail139mailresp,
       |0 as mail139maildownflow,
       |0 as mail139maildowntime,
       |0 as mail139mailFlow,
       |0 as mail139mailTime,
       |0 as MailneteaseMailsucc,
       |0 as MailneteaseMailreq,
       |0 as MailneteaseMailresptimeall,
       |0 as MailneteaseMailresp,
       |0 as MailneteaseMaildownflow,
       |0 as MailneteaseMaildowntime,
       |0 as MailneteaseMailFlow,
       |0 as MailneteaseMailTime,
       |0 as financialsjsjsucc,
       |0 as financialsjsjreq,
       |0 as financialsjsjresptimeall,
       |0 as financialsjsjresp,
       |0 as financialsjsjdownflow,
       |0 as financialsjsjdowntime,
       |0 as financialsjsjFlow,
       |0 as financialsjsjTime,
       |0 as financialsjzqsucc,
       |0 as financialsjzqreq,
       |0 as financialsjzqresptimeall,
       |0 as financialsjzqresp,
       |0 as financialsjzqdownflow,
       |0 as financialsjzqdowntime,
       |0 as financialsjzqFlow,
       |0 as financialsjzqTime,
       |0 as financialzixuangusucc,
       |0 as financialzixuangureq,
       |0 as financialzixuanguresptimeall,
       |0 as financialzixuanguresp,
       |0 as financialzixuangudownflow,
       |0 as financialzixuangudowntime,
       |0 as financialzixuanguFlow,
       |0 as financialzixuanguTime,
       |0 as Othergdqqtsucc,
       |0 as Othergdqqtreq,
       |0 as Othergdqqtresptimeall,
       |0 as Othergdqqtresp,
       |0 as Othergdqqtdownflow,
       |0 as Othergdqqtdowntime,
       |0 as OthergdqqtFlow,
       |0 as OthergdqqtTime,
       |0 as Otherydzssucc,
       |0 as Otherydzsreq,
       |0 as Otherydzsresptimeall,
       |0 as Otherydzsresp,
       |0 as Otherydzsdownflow,
       |0 as Otherydzsdowntime,
       |0 as OtherydzsFlow,
       |0 as OtherydzsTime,
       |0 as Othernxbstsucc,
       |0 as Othernxbstreq,
       |0 as Othernxbstresptimeall,
       |0 as Othernxbstresp,
       |0 as Othernxbstdownflow,
       |0 as Othernxbstdowntime,
       |0 as OthernxbstFlow,
       |0 as OthernxbstTime,
       |0 as Othernxtsucc,
       |0 as Othernxtreq,
       |0 as Othernxtresptimeall,
       |0 as Othernxtresp,
       |0 as Othernxtdownflow,
       |0 as Othernxtdowntime,
       |0 as OthernxtFlow,
       |0 as OthernxtTime,
       |0 as Otheryjtsucc,
       |0 as Otheryjtreq,
       |0 as Otheryjtresptimeall,
       |0 as Otheryjtresp,
       |0 as Otheryjtdownflow,
       |0 as Otheryjtdowntime,
       |0 as OtheryjtFlow,
       |0 as OtheryjtTime,
       |0 as internetvisits,
       |0 as miguvisits,
       |0 as internetbusiness,
       |0 as migubusiness,
       |0 as ServiceIMresptimeall
     """.stripMargin
  val businesskpi=
    s"""
       |sum(case when interface=11 and apptypecode=103 and apptype=15 then 1 else 0 end)browsedownloadvisits,
       |sum(case when interface=11 and (apptypecode=103 or apptypecode=107) and apptype=5 then 1 else 0 end)videoservicevisits,
       |sum(case when interface=11 and apptypecode=103 and apptype=1 then 1 else 0 end)instantmessagevisits,
       |sum(case when interface=11 and apptypecode=103 and apptype=7 then 1 else 0 end)appvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and DLDATA is not null and ULDATA is not null then DLDATA+ULDATA else 0 end )browsedownloadbusiness,
       |sum(case when Interface = 11 and (APPTYPECODE = 103 or APPTYPECODE = 107)and APPTYPE = 5 and DLDATA is not null and ULDATA is not null then DLDATA+ULDATA else 0 end )videoservicebusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and DLDATA is not null and ULDATA is not null then DLDATA+ULDATA else 0 end )instantmessagebusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and DLDATA is not null and ULDATA is not null then DLDATA+ULDATA else 0 end )appbusiness,
       |0 as dnsQuerySucc,
       |0 as dnsQueryAtt,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 and tcplinkstatus=0 then 1 else 0 end)tcpSetupSucc,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then 1 else 0 end)tcpSetupReq,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then ultcppacketre else 0 end)BearerULTCPRetransmit,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then ulippacket else 0 end)BearerULTCPTransmit,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then dltcppacketre else 0 end)BearerDLTCPRetransmit,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then dlippacket else 0 end)BearerDLTCPTransmit,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then ultcppacketor else 0 end)BearerULTCPMissequence,
       |sum(case when interface=11 and apptypecode=103 and l4protocal=0 then dltcppacketor else 0 end)BearerDLTCPMissequence,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE=15 and HTTPFIRSTREDE != 0 and HTTPFIRSTREDE is not null then 1 else 0 end)pageresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE=15 then 1 else 0 end )pagereq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE=15 and HTTPFIRSTREDE != 0 and HTTPFIRSTREDE is not null then HTTPFIRSTREDE else 0 end)pageresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE=15 and HTTPLASTREDE != 0 and HTTPLASTREDE is not null then 1 else 0 end)pageshowsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE=15 and HTTPLASTREDE != 0  and HTTPLASTREDE is not null then HTTPLASTREDE else 0 end)pageshowtimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE=15 and httplastrede is not null and httplastrede<>0 and DLDATA is not null then DLData else 0 end)httpdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and HTTPLASTREDE != 0 and HTTPLASTREDE is not null then (CASE WHEN (HTTPLASTREDE - HTTPFIRSTREDE)<10 then 10 else (HTTPLASTREDE - HTTPFIRSTREDE) END) end)httpdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and APPSTATUS = 0  then 1 else 0 end)mediasucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 then 1 else 0 end)mediareq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and APPSTATUS=0 and DLDATA is not null then DLData else 0 end )mediadownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and APPSTATUS=0 then (procedureendtime - procedurestarttime) else 0 end)mediadowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appstatus=0  then 1 else 0 end)ServiceIMSucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 then 1 else 0 end)ServiceIMReq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2  then 1 else 0 end)readvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3  then 1 else 0 end)wbvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4  then 1 else 0 end)navigationvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6  then 1 else 0 end)musicvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8  then 1 else 0 end)gamevisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9  then 1 else 0 end)payvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10  then 1 else 0 end)Animevisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11  then 1 else 0 end)mailvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12  then 1 else 0 end)p2pvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13  then 1 else 0 end)voipvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14  then 1 else 0 end)MultimediaMsgvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 then 1 else 0 end)financialvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 then 1 else 0 end)securityvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 then 1 else 0 end)shoppingvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 then 1 else 0 end)travelvisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 then 1 else 0 end)cloudstoragevisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 then 1 else 0 end)othervisits,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2  and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)readbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3  and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)wbbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4  and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)navigationbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6  and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)musicbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8  and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)gamebusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9  and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)paybusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)Animebusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)mailbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 and DLDATA is not null and ULDATA is not null  then (uldata+dldata) else 0 end)p2pbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 and DLDATA is not null and ULDATA is not null  then (uldata+dldata) else 0 end)voipbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)MultimediaMsgbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)financialbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)securitybusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)shoppingbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)travelbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)cloudstoragebusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and DLDATA is not null and ULDATA is not null then (uldata+dldata) else 0 end)otherbusiness,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and busrede is not null and busrede<>0 then busrede else 0 end)mediaRespTimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and busrede is not null and busrede<>0 then 1 else 0 end)mediaResp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and busrede is not null and busrede<>0 then 1 else 0 end)ServiceIMTrans,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and httplastrede is not null and httplastrede <>0 and httplastrede<>4294967295 then httplastrede else 0 end)ServiceIMFlow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and httplastrede is not null and httplastrede<>0 and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)ServiceIMTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appstatus=0 then 1 else 0 end)readsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 then 1 else 0 end)readreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and httpfirstrede <>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)readresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and httpfirstrede <>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)readresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and httplastrede <>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)readdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and httplastrede <>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)readdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appstatus=0 then 1 else 0 end)wbsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 then 1 else 0 end)wbreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)wbresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)wbresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)wbdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)wbdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appstatus=0 then 1 else 0 end)navigationsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 then 1 else 0 end)navigationreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)navigationresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)navigationresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)navigationdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)navigationdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appstatus=0 then 1 else 0 end)musicsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 then 1 else 0 end)musicreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)musicresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)musicresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)musicdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)musicdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appstatus=0 then 1 else 0 end)appsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 then 1 else 0 end)appreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)appresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)appresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)appdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)appdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appstatus=0 then 1 else 0 end)gamesucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 then 1 else 0 end)gamereq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)gameresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)gameresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)gamedownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)gamedowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appstatus=0 then 1 else 0 end)paysucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 then 1 else 0 end)payreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)payresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)payresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)paydownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)paydowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appstatus=0 then 1 else 0 end)Animesucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 then 1 else 0 end)Animereq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)Animeresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)Animeresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)Animedownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)Animedowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appstatus=0 then 1 else 0 end)mailsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 then 1 else 0 end)mailreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)mailresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)mailresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)maildownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)maildowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 and appstatus=0 then 1 else 0 end)P2Psucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 then 1 else 0 end)P2Preq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)P2Presptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)P2Presp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)P2Pdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)P2Pdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 and appstatus=0 then 1 else 0 end)VoIPsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 then 1 else 0 end)VoIPreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)VoIPresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)VoIPresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)VoIPdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)VoIPdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 and appstatus=0 then 1 else 0 end)MultimediaMsgsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 then 1 else 0 end)MultimediaMsgreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)MultimediaMsgresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)MultimediaMsgresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)MultimediaMsgdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)MultimediaMsgdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appstatus=0 then 1 else 0 end)financialsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 then 1 else 0 end)financialreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)financialresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)financialresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)financialdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)financialdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 and appstatus=0 then 1 else 0 end)securitysucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 then 1 else 0 end)securityreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)securityresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)securityresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)securitydownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)securitydowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 and appstatus=0 then 1 else 0 end)shoppingsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 then 1 else 0 end)shoppingreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)shoppingresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)shoppingresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)shoppingdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)shoppingdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 and appstatus=0 then 1 else 0 end)travelsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 then 1 else 0 end)travelreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)travelresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)travelresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)traveldownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)traveldowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 and appstatus=0 then 1 else 0 end)cloudstoragesucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 then 1 else 0 end)cloudstoragereq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)cloudstorageresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)cloudstorageresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)cloudstoragedownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)cloudstoragedowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 and appstatus=0 then 1*${onoff} else 0 end)internetsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 then 1*${onoff} else 0 end)internetreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede*${onoff} else 0 end)internetresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1*${onoff} else 0 end)internetresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata*${onoff} else 0 end)internetdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)*${onoff} else 0 end)internetdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 and appstatus=0 then 1*${onoff} else 0 end)migusucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 then 1*${onoff} else 0 end)migureq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede*${onoff} else 0 end)miguresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1*${onoff} else 0 end)miguresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata*${onoff} else 0 end)migudownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)*${onoff} else 0 end)migudowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appstatus=0 then 1 else 0 end)othersucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 then 1 else 0 end)otherreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)otherresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)otherresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata else 0 end)otherdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)otherdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 then (ProcedureEndTime - ProcedureStartTime) else 0 end)instantmessageTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 then (ProcedureEndTime - ProcedureStartTime) else 0 end)readTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 then (ProcedureEndTime - ProcedureStartTime) else 0 end)wbTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 then (ProcedureEndTime - ProcedureStartTime) else 0 end)navigationTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 then (ProcedureEndTime - ProcedureStartTime) else 0 end)videoserviceTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 then (ProcedureEndTime - ProcedureStartTime) else 0 end)musicTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 then (ProcedureEndTime - ProcedureStartTime) else 0 end)appTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 then (ProcedureEndTime - ProcedureStartTime) else 0 end)gameTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 then (ProcedureEndTime - ProcedureStartTime) else 0 end)payTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 then (ProcedureEndTime - ProcedureStartTime) else 0 end)AnimeTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 then (ProcedureEndTime - ProcedureStartTime) else 0 end)mailTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 12 then (ProcedureEndTime - ProcedureStartTime) else 0 end)p2pTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 13 then (ProcedureEndTime - ProcedureStartTime) else 0 end)voipTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 14 then (ProcedureEndTime - ProcedureStartTime) else 0 end)MultimediaMsgTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 then (ProcedureEndTime - ProcedureStartTime) else 0 end)browsedownloadTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 then (ProcedureEndTime - ProcedureStartTime) else 0 end)financialTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 17 then (ProcedureEndTime - ProcedureStartTime) else 0 end)securityTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 18 then (ProcedureEndTime - ProcedureStartTime) else 0 end)shoppingTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 19 then (ProcedureEndTime - ProcedureStartTime) else 0 end)travelTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 20 then (ProcedureEndTime - ProcedureStartTime) else 0 end)cloudstorageTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 then (ProcedureEndTime - ProcedureStartTime)*${onoff} else 0 end)internetTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 then (ProcedureEndTime - ProcedureStartTime)*${onoff} else 0 end)miguTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 then (ProcedureEndTime - ProcedureStartTime) else 0 end)otherTime,
 |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)browsesurfresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 then 1 else 0 end)browsesurfreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)browsesurfresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then 1 else 0 end)browsesurfshowsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then httplastrede else 0 end)browsesurfshowtimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then dldata else 0 end)browsesurfdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)browsesurfdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 and uldata is not null and dldata is not null then uldata+dldata else 0 end)browsesurfFlow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=1 then (ProcedureEndTime - ProcedureStartTime) else 0 end)browsesurfTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)browsetencentresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 then 1 else 0 end)browsetencentreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)browsetencentresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then 1 else 0 end)browsetencentshowsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then httplastrede else 0 end)browsetencentshowtimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then dldata else 0 end)browsetencentdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)browsetencentdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 and uldata is not null and dldata is not null then uldata+dldata else 0 end)browsetencentFlow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=10 then (ProcedureEndTime - ProcedureStartTime) else 0 end)browsetencentTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)browseqqNewsresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 then 1 else 0 end)browseqqNewsreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)browseqqNewsresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then 1 else 0 end)browseqqNewsshowsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then httplastrede else 0 end)browseqqNewsshowtimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then dldata else 0 end)browseqqNewsdownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)browseqqNewsdowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 and uldata is not null and dldata is not null then uldata+dldata else 0 end)browseqqNewsFlow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=78 then (ProcedureEndTime - ProcedureStartTime) else 0 end)browseqqNewsTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)browsetoutiaoresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 then 1 else 0 end)browsetoutiaoreq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)browsetoutiaoresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then 1 else 0 end)browsetoutiaoshowsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then httplastrede else 0 end)browsetoutiaoshowtimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then dldata else 0 end)browsetoutiaodownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)browsetoutiaodowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 and uldata is not null and dldata is not null then uldata+dldata else 0 end)browsetoutiaoFlow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=170 then (ProcedureEndTime - ProcedureStartTime) else 0 end)browsetoutiaoTime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then 1 else 0 end)browseChromeresp,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 then 1 else 0 end)browseChromereq,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and httpfirstrede<>0 and  httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede else 0 end)browseChromeresptimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then 1 else 0 end)browseChromeshowsucc,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then httplastrede else 0 end)browseChromeshowtimeall,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and httplastrede <>0 and  httplastrede  is not null and httplastrede <>4294967295 then dldata else 0 end)browseChromedownflow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end) else 0 end)browseChromedowntime,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 and uldata is not null and dldata is not null then uldata+dldata else 0 end)browseChromeFlow,
       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 15 and appsubtype=7031 then (ProcedureEndTime - ProcedureStartTime) else 0 end)browseChromeTime,
       |""".stripMargin
  val businesskpi1=
    s"""
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)otherhttpresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 then 1  else 0 end)otherhttpreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)otherhttpresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then 1  else 0 end)otherhttpshowsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then httplastrede  else 0 end)otherhttpshowtimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata  else 0 end)otherhttpdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 and httplastrede <>0 and httplastrede  is not null and httplastrede <>4294967295  then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)  else 0 end)otherhttpdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 then dldata + uldata  else 0 end)otherhttpFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=7152 then procedureendtime - procedurestarttime   else 0 end)otherhttpTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1 and appstatus=0   then 1  else 0 end)videomobileTVsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1   then 1  else 0 end)videomobileTVreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede else 0 end)videomobileTVresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1 else 0 end)videomobileTVresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1 and appstatus=0  then 1 else 0 end)videomobileTVdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1  and appstatus=0 then procedureendtime - procedurestarttime   else 0 end)videomobileTVdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1 then uldata + dldata  else 0 end)videomobileTVFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=1  then procedureendtime - procedurestarttime   else 0 end)videomobileTVTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) and appstatus=0 then 1   else 0 end)videomiguvideosucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) then 1   else 0 end)videomiguvideoreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede   else 0 end) videomiguvideoresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1   else 0 end) videomiguvideoresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) and appstatus=0  then dldata   else 0 end)videomiguvideodownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) and appstatus=0  then procedureendtime - procedurestarttime   else 0 end)videomiguvideodowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) then uldata + dldata  else 0 end)videomiguvideoFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 5 and appsubtype in (2,93,7038,7107)) or (APPTYPE = 101 and appsubtype<=5 and appsubtype>=1)  or (appsubtype >= 30 and appsubtype <= 44) or (appsubtype >= 54 and appsubtype <= 58) or (appsubtype in (60,72,146)) or  (appsubtype >= 148 and appsubtype <= 153) or  (appsubtype >= 155 and appsubtype <= 157) ) then procedureendtime - procedurestarttime   else 0 end)videomiguvideoTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 and appstatus=0  then 1  else 0 end)videoiqiyisucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 then 1  else 0 end)videoiqiyireq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)videoiqiyiresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)videoiqiyiresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 and appstatus=0  then dldata  else 0 end)videoiqiyidownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 and appstatus=0  then procedureendtime - procedurestarttime  else 0 end)videoiqiyidowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 then uldata + dldata  else 0 end)videoiqiyiFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 5 and appsubtype=17 then procedureendtime - procedurestarttime  else 0 end)videoiqiyiTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 and appstatus=0  then 1  else 0 end)imFechatsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 then 1  else 0 end)imFechatreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)imFechatresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)imFechatresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata  else 0 end)imFechatdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)  else 0 end)imFechatdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 then dldata + uldata  else 0 end)imFechatFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)imFechatTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) and appstatus=0 then 1  else 0 end)imFetionsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) then 1  else 0 end)imFetionreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)imFetionresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)imFetionresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then uldata  else 0 end)imFetiondownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then case when (httplastrede-httpfirstrede)<10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)imFetiondowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) then uldata + dldata  else 0 end)imFetionFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 1 and appsubtype=2) or (APPTYPE = 100 and appsubtype=11)) then procedureendtime - procedurestarttime  else 0 end)imFetionTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 and appstatus=0 then 1  else 0 end)imQQsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 then 1  else 0 end)imQQreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)imQQresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)imQQresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)imQQdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)imQQdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 then uldata + dldata  else 0 end)imQQFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=5 then procedureendtime - procedurestarttime  else 0 end)imQQTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 and appstatus=0 then 1  else 0 end)imwechatsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 then 1  else 0 end)imwechatreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)imwechatresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)imwechatresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)imwechatdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)imwechatdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 then uldata + dldata  else 0 end)imwechatFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and appsubtype=9 then procedureendtime - procedurestarttime  else 0 end)imwechatTime,
 | |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 and appstatus=0 then 1  else 0 end)readcmreadsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 then 1  else 0 end)readcmreadreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)readcmreadresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)readcmreadresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)readcmreaddownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)readcmreaddowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 then uldata + dldata  else 0 end)readcmreadFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)readcmreadTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 and appstatus=0 then 1  else 0 end)readsurfsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 then 1  else 0 end)readsurfreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)readsurfresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)readsurfresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)readsurfdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)readsurfdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 then uldata + dldata  else 0 end)readsurtFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)readsurfTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 and appstatus=0 then 1  else 0 end)readQQReadsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 then 1  else 0 end)readQQReadreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)readQQReadresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)readQQReadresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)readQQReaddownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)readQQReaddowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 then uldata + dldata  else 0 end)readQQReadFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 2 and appsubtype=5 then procedureendtime - procedurestarttime  else 0 end)readQQReadTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 and appstatus=0 then 1  else 0 end)weibo139wbsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 then 1  else 0 end)weibo139wbreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)weibo139wbresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)weibo139wbresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)weibo139wbdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)weibo139wbdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 then uldata + dldata  else 0 end)weibo139wbFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)weibo139wbTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 and appstatus=0 then 1  else 0 end)weibosinaWeibosucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 then 1  else 0 end)weibosinaWeiboreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)weibosinaWeiboresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)weibosinaWeiboresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)weibosinaWeibodownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)weibosinaWeibodowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 then uldata + dldata  else 0 end)weibosinaWeiboFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 3 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)weibosinaWeiboTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 and appstatus=0 then 1  else 0 end)navigationheMapsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 then 1  else 0 end)navigationheMapreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)navigationheMapresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)navigationheMapresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)navigationheMapdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)navigationheMapdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 then uldata + dldata  else 0 end)navigationheMapFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)navigationheMapTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 and appstatus=0 then 1  else 0 end)navigationaMapsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 then 1  else 0 end)navigationaMapreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)navigationaMapresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)navigationaMapresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)navigationaMapdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)navigationaMapdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 then uldata + dldata  else 0 end)navigationaMapFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 4 and appsubtype=9 then procedureendtime - procedurestarttime  else 0 end)navigationaMapTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) and appstatus=0 then 1  else 0 end)musicmigumusicsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) then 1  else 0 end)musicmigumusicreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)musicmigumusicresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)musicmigumusicresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata  else 0 end)musicmigumusicdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)  else 0 end)musicmigumusicdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) then dldata+uldata  else 0 end)musicmigumusicFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 6 and appsubtype=1) OR (APPTYPE = 101 and appsubtype>=45 AND appsubtype<=53) OR (appsubtype>=63 AND appsubtype<=64) OR (appsubtype>=73 AND appsubtype<=137) ) then procedureendtime - procedurestarttime  else 0 end)musicmigumusicTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 and appstatus=0 then 1  else 0 end)musicqqmusicsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 then 1  else 0 end)musicqqmusicreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)musicqqmusicresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)musicqqmusicresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)musicqqmusicdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)musicqqmusicdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 then uldata + dldata  else 0 end)musicqqmusicFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 6 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)musicqqmusicTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) and appstatus=0 then 1  else 0 end)AppStoreMobileMarketsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) then 1  else 0 end)AppStoreMobileMarketreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)AppStoreMobileMarketresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)AppStoreMobileMarketresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata  else 0 end)AppStoreMobileMarketdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)  else 0 end)AppStoreMobileMarketdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) then uldata + dldata  else 0 end) AppStoreMobileMarketFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ( (APPTYPE = 7 and appsubtype=1) OR (APPTYPE = 100 and appsubtype in (6,7,8,9)) ) then procedureendtime - procedurestarttime  else 0 end) AppStoreMobileMarketTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 and appstatus=0 then 1  else 0 end)AppStoreAppStoresucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 then 1  else 0 end)AppStoreAppStorereq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)AppStoreAppStoreresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)AppStoreAppStoreresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)AppStoreAppStoredownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)AppStoreAppStoredowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 then uldata + dldata  else 0 end)AppStoreAppStoreFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 7 and appsubtype=3 then procedureendtime - procedurestarttime  else 0 end)AppStoreAppStoreTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 and appstatus=0 then 1  else 0 end)GamemiguGamesucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 then 1  else 0 end)GamemiguGamereq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)GamemiguGameresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)GamemiguGameresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)GamemiguGamedownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)GamemiguGamedowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 then uldata + dldata  else 0 end)GamemiguGameFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)GamemiguGameTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 and appstatus=0 then 1  else 0 end)Game4399Gamesucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 then 1  else 0 end)Game4399Gamereq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)Game4399Gameresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)Game4399Gameresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)Game4399Gamedownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)Game4399Gamedowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 then uldata + dldata  else 0 end)Game4399GameFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 8 and appsubtype=42 then procedureendtime - procedurestarttime  else 0 end)Game4399GameTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 and appstatus=0 then 1  else 0 end)PayNFCSIMsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 then 1  else 0 end)PayNFCSIMreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)PayNFCSIMresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)PayNFCSIMresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)PayNFCSIMdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)PayNFCSIMdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 then uldata + dldata  else 0 end)PayNFCSIMFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)PayNFCSIMTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 and appstatus=0 then 1  else 0 end)PaymobilePaysucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 then 1  else 0 end)PaymobilePayreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)PaymobilePayresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)PaymobilePayresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)PaymobilePaydownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)PaymobilePaydowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 then uldata + dldata  else 0 end)PaymobilePayFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)PaymobilePayTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 and appstatus=0 then 1  else 0 end)PayNFCsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 then 1  else 0 end)PayNFCreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)PayNFCresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)PayNFCresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)PayNFCdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)PayNFCdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 then uldata + dldata  else 0 end)PayNFCFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 9 and appsubtype=3 then procedureendtime - procedurestarttime  else 0 end)PayNFCTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) and appstatus=0 then 1  else 0 end)Animemigudmsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) then 1  else 0 end)Animemigudmreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)Animemigudmresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)Animemigudmresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata  else 0 end)Animemigudmdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)  else 0 end)Animemigudmdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) then uldata+dldata  else 0 end)AnimemigudmFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 10 and appsubtype=1)  OR (APPTYPE = 101 and appsubtype in (138,139,140,141,142,143,144,145)) ) then procedureendtime - procedurestarttime  else 0 end)AnimemigudmTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 and appstatus=0 then 1  else 0 end)AnimeqqComicsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 then 1  else 0 end)AnimeqqComicreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)AnimeqqComicresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)AnimeqqComicresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)AnimeqqComicdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)AnimeqqComicdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 then uldata + dldata  else 0 end)AnimeqqComicFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 10 and appsubtype=12 then procedureendtime - procedurestarttime  else 0 end)AnimeqqComicTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) and appstatus=0 then 1  else 0 end)mail139mailsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) then 1  else 0 end)mail139mailreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then httpfirstrede  else 0 end)mail139mailresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295 then 1  else 0 end)mail139mailresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then dldata  else 0 end)mail139maildownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295 then (case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end)  else 0 end)mail139maildowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) then uldata+dldata  else 0 end)mail139mailFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and ((APPTYPE = 11 and appsubtype=1)  OR (APPTYPE = 100 and appsubtype in (1,2,3,4,5)) ) then procedureendtime - procedurestarttime  else 0 end)mail139mailTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 and appstatus=0 then 1  else 0 end)MailneteaseMailsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 then 1  else 0 end)MailneteaseMailreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)MailneteaseMailresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)MailneteaseMailresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)MailneteaseMaildownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)MailneteaseMaildowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 then uldata + dldata  else 0 end)MailneteaseMailFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 11 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)MailneteaseMailTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 and appstatus=0 then 1  else 0 end)financialsjsjsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 then 1  else 0 end)financialsjsjreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)financialsjsjresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)financialsjsjresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)financialsjsjdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)financialsjsjdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 then uldata + dldata  else 0 end)financialsjsjFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)financialsjsjTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 and appstatus=0 then 1  else 0 end)financialsjzqsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 then 1  else 0 end)financialsjzqreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)financialsjzqresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)financialsjzqresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)financialsjzqdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)financialsjzqdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 then uldata + dldata  else 0 end)financialsjzqFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)financialsjzqTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 and appstatus=0 then 1  else 0 end)financialzixuangusucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 then 1  else 0 end)financialzixuangureq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)financialzixuanguresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)financialzixuanguresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)financialzixuangudownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)financialzixuangudowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 then uldata + dldata  else 0 end)financialzixuanguFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 16 and appsubtype=47 then procedureendtime - procedurestarttime  else 0 end)financialzixuanguTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 and appstatus=0 then 1  else 0 end)Othergdqqtsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 then 1  else 0 end)Othergdqqtreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)Othergdqqtresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)Othergdqqtresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)Othergdqqtdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)Othergdqqtdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 then uldata + dldata  else 0 end)OthergdqqtFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=1 then procedureendtime - procedurestarttime  else 0 end)OthergdqqtTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 and appstatus=0 then 1  else 0 end)Otherydzssucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 then 1  else 0 end)Otherydzsreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)Otherydzsresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)Otherydzsresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)Otherydzsdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)Otherydzsdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 then uldata + dldata  else 0 end)OtherydzsFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=2 then procedureendtime - procedurestarttime  else 0 end)OtherydzsTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 and appstatus=0 then 1  else 0 end)Othernxbstsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 then 1  else 0 end)Othernxbstreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)Othernxbstresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)Othernxbstresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)Othernxbstdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)Othernxbstdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 then uldata + dldata  else 0 end)OthernxbstFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=3 then procedureendtime - procedurestarttime  else 0 end)OthernxbstTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 and appstatus=0 then 1  else 0 end)Othernxtsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 then 1  else 0 end)Othernxtreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)Othernxtresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)Othernxtresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)Othernxtdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)Othernxtdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 then uldata + dldata  else 0 end)OthernxtFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=4 then procedureendtime - procedurestarttime  else 0 end)OthernxtTime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 and appstatus=0 then 1  else 0 end)Otheryjtsucc,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 then 1  else 0 end)Otheryjtreq,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then httpfirstrede  else 0 end)Otheryjtresptimeall,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 and httpfirstrede<>0 and httpfirstrede is not null and httpfirstrede<>4294967295  then 1  else 0 end)Otheryjtresp,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then dldata  else 0 end)Otheryjtdownflow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 and httplastrede<>0 and httplastrede is not null and httplastrede<>4294967295  then case when (httplastrede-httpfirstrede)<=10 then httpfirstrede else (httplastrede-httpfirstrede) end  else 0 end)Otheryjtdowntime,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 then uldata + dldata  else 0 end)OtheryjtFlow,
       |       |sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 21 and appsubtype=5 then procedureendtime - procedurestarttime  else 0 end)OtheryjtTime,
       |       sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 then 1 else 0 end)internetvisits,
       |       sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 then 1 else 0 end)miguvisits,
       |       sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 100 and uldata is not null and dldata is not null then uldata+dldata else 0 end)internetbusiness,
       |       sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 101 and uldata is not null and dldata is not null then uldata+dldata else 0 end)migubusiness,
       |       sum(case when Interface = 11 and APPTYPECODE = 103 and APPTYPE = 1 and httpfirstrede is not null and httpfirstrede<>0 and httpfirstrede<>4294967295 then httpfirstrede else 0 end)ServiceIMresptimeall
     """.stripMargin
  def analyse(implicit sparkSession: SparkSession): Unit = {
    tacHourAnalyse(sparkSession)
    cellHourAnalyse(sparkSession)
    spHourAnalyse(sparkSession)
    ueHourAnalyse(sparkSession)
    sgwHourAnalyse(sparkSession)
    imsicellHourAnalyse(sparkSession)
  }

  def tacHourAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(s"""alter table tac_hour_http add if not exists partition(dt=$ANALY_DATE,h=$ANALY_HOUR)"""
      )
    val http=sql(
      s"""
         |select
         |substring(imei,1,8)tac,
         |$businesskpi
         |$businesskpi1
         |from $SDB.tb_xdr_ifc_http where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by imei
       """.stripMargin)
    val dns=sql(
        s"""
           |select
           |substring(imei,1,8)tac,
           |$busniessDns
           |from
           |$SDB.tb_xdr_ifc_dns where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by imei
         """.stripMargin
      )
    http.union(dns).createOrReplaceTempView("tactmp")
        sql(
          s"""
             |select
             |'$cal_date',
             |tac,
             |$kpibusinesssum
             |from
             |tactmp
             |group by tac
           """.stripMargin
        ).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/tac_hour_http/dt=$ANALY_DATE/h=$ANALY_HOUR")
  }

  def cellHourAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(
      s"""alter table cell_hour_http add if not exists partition(dt=$ANALY_DATE,h=$ANALY_HOUR)""".stripMargin)
    val http=sql(
      s"""
         |select
         |ecgi,
         |$businesskpi
         |$businesskpi1
         |from $SDB.tb_xdr_ifc_http where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by ecgi

       """.stripMargin)
    val dns=sql(
      s"""
         |select
         |ecgi,
         |$busniessDns
         |from $SDB.tb_xdr_ifc_dns where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by ecgi
         """.stripMargin
    )
    http.union(dns).createOrReplaceTempView("ecgitmp")
     sql(
       s"""
          |select
          |'$cal_date',
          |ecgi,
          |$kpibusinesssum
          |from
          |ecgitmp
          |group by ecgi
        """.stripMargin
     ).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/cell_hour_http/dt=$ANALY_DATE/h=$ANALY_HOUR")
  }

  def spHourAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(
      s"""alter table sp_hour_http add if not exists partition(dt=$ANALY_DATE,h=$ANALY_HOUR)""".stripMargin)
    val http=sql(
      s"""
         |select
         |appserveripipv4 as sp,
         |$businesskpi
         |$businesskpi1
         |from $SDB.tb_xdr_ifc_http where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by appserveripipv4
       """.stripMargin)
    val dns=sql(
      s"""
         |select
         |appserveripipv4 as sp,
         |$busniessDns
         |from $SDB.tb_xdr_ifc_dns where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by appserveripipv4
         """.stripMargin
    )
      http.union(dns).createOrReplaceTempView("sptmp")
    sql(
      s"""
         |select
         |'$cal_date',
         |sp,
         |$kpibusinesssum
         |from
         |sptmp
         |group by sp
       """.stripMargin)
      .write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/sp_hour_http/dt=$ANALY_DATE/h=$ANALY_HOUR")
  }

  def ueHourAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(
      s"""alter table ue_hour_http add if not exists partition(dt=$ANALY_DATE,h=$ANALY_HOUR)""".stripMargin)
    val http= sql(
      s"""
         |select
         |imsi,
         |msisdn,
         |$businesskpi
         |$businesskpi1
         |from $SDB.tb_xdr_ifc_http where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by imsi,msisdn
       """.stripMargin)
    val dns=sql(
      s"""
         |select
         |imsi,
         |msisdn,
         |$busniessDns
         |from $SDB.tb_xdr_ifc_dns where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by imsi,msisdn
         """.stripMargin
    )
    http.union(dns).createOrReplaceTempView("uetmp")
      sql(
        s"""
          |select
          |'$cal_date',
          |imsi,
          |msisdn,
          |$kpibusinesssum
          |from
          |uetmp
          |group by
          |imsi,
          |msisdn
        """.stripMargin)
      .write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/ue_hour_http/dt=$ANALY_DATE/h=$ANALY_HOUR")
  }

  def sgwHourAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(
      s"""alter table sgw_hour_http add if not exists partition(dt=$ANALY_DATE,h=$ANALY_HOUR)""".stripMargin)
    val http=sql(
      s"""
         |select
         |sgwipaddr,
         |$businesskpi
         |$businesskpi1
         |from $SDB.tb_xdr_ifc_http where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by sgwipaddr
       """.stripMargin)
    val dns=sql(
      s"""
         |select
         |sgwipaddr,
         |$busniessDns
         |from $SDB.tb_xdr_ifc_dns where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by sgwipaddr
         """.stripMargin
    )
      http.union(dns).createOrReplaceTempView("sgwtmp")
    sql(
      s"""
         |select
         |'$cal_date',
         |sgwipaddr,
         |$kpibusinesssum
         |from
         |sgwtmp
         |group by sgwipaddr
        """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/sgw_hour_http/dt=$ANALY_DATE/h=$ANALY_HOUR")
  }

  def imsicellHourAnalyse(implicit sparkSession: SparkSession): Unit = {
    import sparkSession.sql
    sql(s"use $DDB")
    sql(
      s"""alter table imsi_cell_hour_http add if not exists partition(dt=$ANALY_DATE,h=$ANALY_HOUR)""".stripMargin)
    val http=sql(
      s"""
         |select
         |imsi,
         |msisdn,
         |ecgi,
         |$businesskpi
         |$businesskpi1
         |from $SDB.tb_xdr_ifc_http where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by imsi,msisdn,ecgi
       """.stripMargin)

    val dns=sql(
      s"""
         |select
         |imsi,
         |msisdn,
         |ecgi,
         |$busniessDns
         |from $SDB.tb_xdr_ifc_dns where dt="$ANALY_DATE" and h="$ANALY_HOUR" group by imsi,msisdn,ecgi
         """.stripMargin
    )
    http.union(dns).createOrReplaceTempView("imsicelltmp")
    sql(
      s"""
         |select
         |'$cal_date',
         |imsi,
         |msisdn,
         |ecgi,
         |$kpibusinesssum
         |from
         |imsicelltmp
         |group by imsi,
         |msisdn,
         |ecgi
        """.stripMargin).write.mode(SaveMode.Overwrite).csv(s"$warhouseDir/imsi_cell_hour_http/dt=$ANALY_DATE/h=$ANALY_HOUR")
  }

}

